﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using DbAccessLib;

namespace DataAccess
{
    public class DiaDiemDAO 
    {
        private static DiaDiemDAO m_DiaDiemDAO = new DiaDiemDAO();

        public static DiaDiemDAO DAO
        {
            get { return m_DiaDiemDAO; }
        }

        private DiaDiemDAO()
        {
        }

        public DataTable tracuuDiaDiemDichVuTheoTenDuong(String  TenDichVu , String TenDuong )
        {
            String CommandText_Select = " SELECT tdd.TenDiaDiem ,dv.TenDichVu ,dl.SoNha, d.TenDuong ,  p.TenPhuong ,dl.KinhDo , dl.ViDo ,dl.ChuThich ";
            String CommandText_FromTenDiaDiem = " FROM ((((DULIEU dl INNER JOIN TENDIADIEM tdd ON dl.MaTenDiaDiem = tdd.MaTenDiaDiem )";
            String CommandText_FromPhuong = " INNER JOIN PHUONG p ON p.MaPhuong = dl.MaPhuong )";
            String CommandText_FromDuong = " INNER JOIN DUONG d ON d.MaDuong = dl.MaDuong ) ";
            String CommandText_FromDichVu = " INNER JOIN DICHVU dv ON dv.MaDichVu =  dl.MaDichVu ) ";
            String CommandText_Where = " WHERE dl.MaQuanHuyen = 31 and  dv.TenDichVu = ? and d.TenDuong like ?;";

            String CommandText = CommandText_Select + CommandText_FromTenDiaDiem + CommandText_FromPhuong + CommandText_FromDuong + CommandText_FromDichVu + CommandText_Where;

            SQLStatement cmd = Db.DiaDiemDB.CreateStatement(CommandText);

            cmd.bindParam("?", TenDichVu, OleDbType.VarChar);
            cmd.bindParam("?", TenDuong, OleDbType.VarChar);

            return cmd.ExecuteQuery();
        }

        public DataTable tracuuDiaDiemDichVuTheoTenDiaDiem(String TenDichVu, String TenDiaDiem)
        {
            String CommandText_Select = " SELECT tdd.TenDiaDiem ,dv.TenDichVu ,dl.SoNha, d.TenDuong ,  p.TenPhuong ,dl.KinhDo , dl.ViDo ,dl.ChuThich ";
            String CommandText_FromTenDiaDiem = " FROM ((((DULIEU dl INNER JOIN TENDIADIEM tdd ON dl.MaTenDiaDiem = tdd.MaTenDiaDiem )";
            String CommandText_FromPhuong = " INNER JOIN PHUONG p ON p.MaPhuong = dl.MaPhuong )";
            String CommandText_FromDuong = " INNER JOIN DUONG d ON d.MaDuong = dl.MaDuong ) ";
            String CommandText_FromDichVu = " INNER JOIN DICHVU dv ON dv.MaDichVu =  dl.MaDichVu ) ";
            String CommandText_Where = " WHERE dl.MaQuanHuyen = 31 and dv.TenDichVu = ? and tdd.TenDiaDiem = ?;";

            String CommandText = CommandText_Select + CommandText_FromTenDiaDiem + CommandText_FromPhuong + CommandText_FromDuong + CommandText_FromDichVu + CommandText_Where;

            SQLStatement cmd = Db.DiaDiemDB.CreateStatement(CommandText);

            cmd.bindParam("?", TenDichVu, OleDbType.VarChar);
            cmd.bindParam("?", TenDiaDiem, OleDbType.VarChar);

            return cmd.ExecuteQuery();
        }


        public DataTable tracuuDiaDiemDichVu(String TenDichVu, String TenDuong, String TenDiaDiem)
        {
            String CommandText_Select = " SELECT tdd.TenDiaDiem ,dv.TenDichVu ,dl.SoNha, d.TenDuong ,  p.TenPhuong ,dl.KinhDo , dl.ViDo ,dl.ChuThich ";
            String CommandText_FromTenDiaDiem = " FROM ((((DULIEU dl INNER JOIN TENDIADIEM tdd ON dl.MaTenDiaDiem = tdd.MaTenDiaDiem )";
            String CommandText_FromPhuong = " INNER JOIN PHUONG p ON p.MaPhuong = dl.MaPhuong )";
            String CommandText_FromDuong = " INNER JOIN DUONG d ON d.MaDuong = dl.MaDuong ) ";
            String CommandText_FromDichVu = " INNER JOIN DICHVU dv ON dv.MaDichVu =  dl.MaDichVu ) ";
            String CommandText_Where = " WHERE dl.MaQuanHuyen = 31 and dv.TenDichVu = ? and d.TenDuong like ? and tdd.TenDiaDiem = ?;";

            String CommandText = CommandText_Select + CommandText_FromTenDiaDiem + CommandText_FromPhuong + CommandText_FromDuong + CommandText_FromDichVu + CommandText_Where;

            SQLStatement cmd = Db.DiaDiemDB.CreateStatement(CommandText);
            cmd.bindParam("?", TenDichVu, OleDbType.VarChar);
            cmd.bindParam("?", TenDuong, OleDbType.VarChar);
            cmd.bindParam("?", TenDiaDiem , OleDbType.VarChar);

            return cmd.ExecuteQuery();
        }

        public DataTable tracuuDiaDiemDichVu(String TenDichVu)
        {
            String CommandText_Select = " SELECT tdd.TenDiaDiem ,dv.TenDichVu ,dl.SoNha, d.TenDuong ,  p.TenPhuong ,dl.KinhDo , dl.ViDo ,dl.ChuThich ";
            String CommandText_FromTenDiaDiem = " FROM ((((DULIEU dl INNER JOIN TENDIADIEM tdd ON dl.MaTenDiaDiem = tdd.MaTenDiaDiem )";
            String CommandText_FromPhuong = " INNER JOIN PHUONG p ON p.MaPhuong = dl.MaPhuong )";
            String CommandText_FromDuong = " INNER JOIN DUONG d ON d.MaDuong = dl.MaDuong ) ";
            String CommandText_FromDichVu = " INNER JOIN DICHVU dv ON dv.MaDichVu =  dl.MaDichVu ) ";
            String CommandText_Where = " WHERE dl.MaQuanHuyen = 31 and dv.TenDichVu = ?;";

            String CommandText = CommandText_Select + CommandText_FromTenDiaDiem + CommandText_FromPhuong + CommandText_FromDuong + CommandText_FromDichVu + CommandText_Where;

            SQLStatement cmd = Db.DiaDiemDB.CreateStatement(CommandText);
            cmd.bindParam("?", TenDichVu, OleDbType.VarChar);

            return cmd.ExecuteQuery();
        }


    }
}
